﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace _1___NorthwindConnection
{
    class NorthwindDAO
    {
        private static Random rand = new Random();

        private static string GenerateKey()
        {
            NorthwindEntities context = new NorthwindEntities();
            List<string> keysList =
                (from c in context.Customers
                select c.CustomerID).ToList();

            HashSet<string> keys = new HashSet<string>();

            foreach (var item in keysList)
            {
                keys.Add(item.ToString());
            }

            char[] key = new char[5];
            while (true)
            {
                for (int i = 0; i < 5; i++)
                {
                    key[i] = (char)rand.Next(65, 91);
                }
                string k = new string(key, 0, 5);
                if(!keys.Contains(k))
                    return k;
            }
        }

        public static string InsertCustomer(string companyName, string contactName, string contactTitle,
            string address, string city, string region, string postalCode, string country, string phone, string fax)
        {
            NorthwindEntities north = new NorthwindEntities();
            string ID = GenerateKey();
          
            Customer toAdd = new Customer()
            {
                Address = address,
                City = city,
                CompanyName = companyName,
                ContactName = contactName,
                ContactTitle = contactTitle,
                Country = country,
                PostalCode = postalCode,
                Region = region,
                Phone = phone,
                Fax = fax,
                CustomerID = ID
            };

            north.Customers.AddObject(toAdd);
            north.SaveChanges();

            return ID;
        }

        public static void DeleteCustomer(string ID)
        {
            NorthwindEntities north = new NorthwindEntities();
            Customer custToDelete = north.Customers.FirstOrDefault(x => x.CustomerID == ID);
            if (custToDelete != null)
            {
                north.Customers.DeleteObject(custToDelete);
                north.SaveChanges();
                Console.WriteLine();
            }
        }

        public static void UpdateCustomer(string custID)
        {
            NorthwindEntities north = new NorthwindEntities();            
        }

        public static void ShippedToCanada()
        {
            NorthwindEntities north = new NorthwindEntities();
            var cust =
                from c in north.Customers
                join o in north.Orders
                    on c.CustomerID equals o.CustomerID
                where o.ShipCountry=="Canada"
                select new
                {
                    CustomerName = c.CompanyName,
                    Date = o.OrderDate,
                    Country = o.ShipCountry
                };
            foreach (var item in cust)
            {
                Console.WriteLine("{0}, {1}, {2}", item.CustomerName,item.Country, item.Date);
            }
        }
    }
}

